/*
 * retrieves information based on the choise of the user whether city, zip-code,
 * building or organ maker.
 */
var querystring = require("querystring"), fs = require("fs"), Client = require('mysql').Client;
var Client = require('mysql').Client;
var mysql = require('mysql');

/*
 * where all search requests are handled: based on the search criteria recieved from the user
 * queries are performed to the database and response is sent
 */
exports.main = function(req, res){
	
	var client = new Client(); 
	client.user = 'morgan';
	client.password = 'm0rgan$$$';
	client.host="127.0.0.1";
	//client.port="3306";
	client.database="orgeldb"
	
		var list_records = ""
	   var query = "SELECT * from kanton where id>0 AND id<31 AND id!=8 AND id!=9 or id= '' ;";

		
	 	client.query(query, function selectCb(err, results, fields) {
    		if (err) {
      			throw err;
    		}
    		client.end();
    		 list_records = JSON.stringify(results);
	
		});
	
  	 var array;
  	 var requestBody;
  	 var ortField;
  	 var querysearch;
  	 if(req.body.city){
  	  	array=[req.body.city]
  	  	requestBody = array[0];
  	    ortField = "o.name"; 
  	    var query = "SELECT DISTINCT m.id, o.name AS ort, o.plz, MIN( d.bezeichnung ) datum , m.gebaeude, k.name AS name, k.synonyme AS synonyme, k.vorname AS vorname "+
		"FROM massnahme AS m INNER JOIN massnahme_kontakt AS mk ON m.id = mk.massnahme "+
		"INNER JOIN kontakt AS k ON mk.kontakt = k.id LEFT OUTER JOIN datum AS d ON m.massnahmeDatum = d.id "+
		"INNER JOIN ort AS o ON m.ort = o.id INNER JOIN kanton AS kn ON o.kanton = kn.id WHERE " +ortField+ " = '"+requestBody+"' "+
		"GROUP BY m.gebaeude "
	 	client.query(query, function selectCb(err, results, fields) {
    		if (err) {
      			throw err;
    		}
    		client.end();
    		var records = JSON.stringify(results);
    		res.render('results.ejs', {
    			search: records,
    			kantons: list_records
    		})
 		});
  	 }
  	 else if(req.body.code){
  	 	array=[req.body.code]
  	 	requestBody = array[0];
  	    ortField = "o.plz"; 
  	    var query = "SELECT DISTINCT m.id, o.name AS ort, o.plz, MIN( d.bezeichnung ) datum, m.gebaeude, k.name AS name, k.synonyme AS synonyme, k.vorname AS vorname "+
		"FROM massnahme AS m INNER JOIN massnahme_kontakt AS mk ON m.id = mk.massnahme "+
		"INNER JOIN kontakt AS k ON mk.kontakt = k.id LEFT OUTER JOIN datum AS d ON m.massnahmeDatum = d.id "+
		"INNER JOIN ort AS o ON m.ort = o.id INNER JOIN kanton AS kn ON o.kanton = kn.id WHERE " +ortField+ " = '"+requestBody+"' "+
		"GROUP BY m.gebaeude "
	 	client.query(query, function selectCb(err, results, fields) {
    		if (err) {
      			throw err;
    		}
    		client.end();
    		var records = JSON.stringify(results);
    		res.render('results.ejs', {
    			search: records,
    			kantons: list_records
    		})
 		});
  	 } 
  	 else if (req.body.building) {
  	 	array=[req.body.building]
  	 	requestBody = array[0];
  	    ortField = "m.gebaeude"; 
  	  var query = "SELECT DISTINCT m.id, o.name AS ort, o.plz, MIN( d.bezeichnung ) datum , m.gebaeude, k.name AS name, k.synonyme AS synonyme, k.vorname AS vorname "+
		"FROM massnahme AS m INNER JOIN massnahme_kontakt AS mk ON m.id = mk.massnahme "+
		"INNER JOIN kontakt AS k ON mk.kontakt = k.id LEFT OUTER JOIN datum AS d ON m.massnahmeDatum = d.id "+
		"INNER JOIN ort AS o ON m.ort = o.id INNER JOIN kanton AS kn ON o.kanton = kn.id WHERE " +ortField+ " like '%"+requestBody+"%' "+
		"GROUP BY m.gebaeude "
	 	client.query(query, function selectCb(err, results, fields) {
    		if (err) {
      			throw err;
    		}
    		client.end();
    		var records = JSON.stringify(results);
    		res.render('results.ejs', {
    			search: records,
    			kantons: list_records
    		})
 		});
  	 } 
  	 
  	 else if(req.body.Organ){
  	 	array=[req.body.Organ]
  	  	requestBody = array[0];
  	    ortField = "k.name"; 
  	   var query = "SELECT DISTINCT m.id, o.name AS ort, o.plz, MIN( d.bezeichnung ) datum , m.gebaeude, k.name AS name, k.synonyme AS synonyme, k.vorname AS vorname "+
		"FROM massnahme AS m INNER JOIN massnahme_kontakt AS mk ON m.id = mk.massnahme "+
		"INNER JOIN kontakt AS k ON mk.kontakt = k.id LEFT OUTER JOIN datum AS d ON m.massnahmeDatum = d.id "+
		"INNER JOIN ort AS o ON m.ort = o.id INNER JOIN kanton AS kn ON o.kanton = kn.id WHERE " +ortField+ " like '%"+requestBody+"%' "+
		"GROUP BY m.gebaeude "
	 	client.query(query, function selectCb(err, results, fields) {
    		if (err) {
      			throw err;
    		}
    		client.end();
    		var records = JSON.stringify(results);
    		res.render('results.ejs', {
    			search: records,
    			kantons: list_records
    		})
 		});
	}
	else if (req.body.State) {
  	 	array=[req.body.State]
  	 	requestBody = array[0];
  	    ortField = "kn.name"; 
   	   var query = "SELECT DISTINCT m.id, o.name AS ort, o.plz, MIN( d.bezeichnung ) datum , m.gebaeude, k.name AS name, k.synonyme AS synonyme, k.vorname AS vorname "+
		"FROM massnahme AS m INNER JOIN massnahme_kontakt AS mk ON m.id = mk.massnahme "+
		"INNER JOIN kontakt AS k ON mk.kontakt = k.id LEFT OUTER JOIN datum AS d ON m.massnahmeDatum = d.id "+
		"INNER JOIN ort AS o ON m.ort = o.id INNER JOIN kanton AS kn ON o.kanton = kn.id WHERE " +ortField+ " like '%"+requestBody+"%' "+
		"GROUP BY m.gebaeude ORDER BY ort, m.gebaeude ASC"
	 	client.query(query, function selectCb(err, results, fields) {
    		if (err) {
      			throw err;
    		}
    		client.end();
    		var records = JSON.stringify(results);
    		res.render('results.ejs', {
    			search: records,
    			kantons: list_records
    		})
 		});
  	 } 
		
  
 	
};
